<?php
class DbMysql extends Db{

	function __construct($dbconfig=''){
		$this->dbconfig = $dbconfig;
	}


	//創建連接
	public function connect($mode){
		$config = $this->dbconfig[$mode];
		$dbhost = $config['dbhost'];
		$dbname = $config['dbname'];
		$dbuser = $config['username'];
		$dbpasswd  = $config['password'];
		$pconnect = $config['pconnect'];
		$charset = isset($config['charset'])? $config['charset'] : '';

		$dsn  = "mysql:host=$dbhost;dbname=$dbname;";
		try {
			$h_param = array(
				PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
			);
			if($charset!=''){
				$h_param[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES '.$charset;	#設置默認編碼
			}
			if ($pconnect){
				$h_param[PDO::ATTR_PERSISTENT] = true;
			}
			$conn = new PDO($dsn, $dbuser, $dbpasswd, $h_param);
		} catch (PDOException $e) {
			throw new ErrorException('Unable to connect to db server. Error:'.$e->getMessage(), 31);
		}
		return $conn;
	}


	/** 查詢DB
	* @param String 	$tabname 	表名
	* @param Array		$param	 	參數
	* @param obj		$dbconn		DB link
	* @param String		$fetchmode	PDO:setFetchMode
	* @param int		$debug		0:不顯示查詢語句 1:顯示查詢語句
	* @return Array 	$result $pageinfo
	*/
	public function query($tabname,$param=array(),$dbconn,$fetchmode='',$debug=0){

		$field = isset($param['field']) && !empty($param['field'])? $param['field'] : '*';
		$where = isset($param['where']) && !empty($param['where'])? 'where '.$param['where'] : '';
		$group = isset($param['group']) && !empty($param['group'])? 'group by '.$param['group'] : '';
		$order = isset($param['order']) && !empty($param['order'])? 'order by '.$param['order'] : '';
		$limit = isset($param['limit']) && !empty($param['limit'])? 'limit '.$param['limit'] : '';
		$pagesize = isset($param['pagesize']) && !empty($param['pagesize'])? $param['pagesize'] : 0;
		$page = isset($param['page']) && !empty($param['page'])? $param['page'] : 1;
		$join = isset($param['join']) && !empty($param['join'])? $param['join'].' as TB2' : '';
		$on = isset($param['on']) && !empty($param['on'])? 'on '.$param['on'] : '';
		$getrow = preg_match("/group by/i",$group)? 'rowCount' : 'fetch';

		if($pagesize>0){	//分頁
			if($join && $on){
				$this->debug($debug,"SELECT count(*) from $tabname as TB1 $join $on $where $group");	//debug
				$query = $dbconn->query("SELECT count(*) from $tabname as TB1 $join $on $where $group")->$getrow();
			}else{
				$this->debug($debug,"SELECT count(*) from $tabname $where $group");						//debug
				$query = $dbconn->query("SELECT count(*) from $tabname $where $group")->$getrow();
			}
			$total = is_array($query)? array_pop($query) : $query;	//記錄總數
			$pagecount = ($total>0)? (int)(($total-1)/$pagesize)+1 : 0;
			if(!is_numeric($page) || $page<=0 || $page>$pagecount){
				$page = 1;
			}
			$pageinfo = array();
			$pageinfo['total'] = $total;
			$pageinfo['pagecount'] = $pagecount;
			$pageinfo['pagesize'] = $pagesize;
			$pageinfo['curpage'] = $page;

			$startpage = ($page-1)*$pagesize;
			if($join && $on){
				$sqlstr = "select $field from $tabname as TB1 $join $on $where $group $order limit $startpage,$pagesize";
			}else{
				$sqlstr = "select $field from $tabname $where $group $order limit $startpage,$pagesize";			
			}
		}else{	//不分頁
			if($join && $on){
				$sqlstr = "select $field from $tabname as TB1 $join $on $where $group $order $limit";
			}else{
				$sqlstr = "select $field from $tabname $where $group $order $limit";			
			}
		}
		$this->debug($debug,$sqlstr);		//debug

		$sth = $dbconn->prepare($sqlstr);
		$sth->execute();

		switch($fetchmode){
			case 'num':
				$result = $sth->fetchAll(PDO::FETCH_NUM);
				break;
			case 'hash':
				$result = $sth->fetchAll(PDO::FETCH_ASSOC);
				break;
			case 'one':
				$result = $sth->fetch();
				break;
			default:
				$result = $sth->fetchAll();
		}

		$data = array();
		$data['result'] = $result;
		$data['pageinfo'] = isset($pageinfo)? $pageinfo : array();
		return $data;
	}


	/** 插入記錄
	* @param String	$tabname	表名
	* @param Array	$data		key=>$value
	* @param obj	$dbconn		DB link
	* @return int insertID
	*/
	public function add($tabname,$data,$dbconn){
		$a_add_placeholder = array();
		foreach($data as $a_add_field[] =>$a_add_value[]){
			array_push($a_add_placeholder,'?');
		}
		$add_part         = '`'.implode('`,`', $a_add_field).'`';
		$placeholder_part = implode(',', $a_add_placeholder);
		$sth = $dbconn->prepare("insert into $tabname ($add_part) values ($placeholder_part)");
		$sth->execute($a_add_value);
		return $dbconn->lastInsertId();
	}


	/** 更新記錄
	* @param String $tabname	表名
	* @param Array	$data		$key=>$value
	* @param String	$condition	條件
	* @param obj	$dbconn		DB link
	* @return int affected count
	*/
	public function update($tabname,$data,$condition='',$dbconn){
		$a_update_field = array();
		$a_update_value = array();

		foreach($data as $colname=>$a_update_value[]){
			array_push($a_update_field,"`$colname`=?");
		}
		$update = implode(',',$a_update_field);
		if(!empty($condition)){
			$sth = $dbconn->prepare("update $tabname set $update where $condition");
		}else{
			$sth = $dbconn->prepare("update $tabname set $update");			
		}
		$sth->execute($a_update_value);
		$rows_affected = $sth->rowCount();
		return $rows_affected;
	}
	
	
	/** 刪除記錄
	* @param String $tabname	表名
	* @param String $condition	條件
	* @param obj	$dbconn		DB link
	*/
	public function del($tabname,$condition,$dbconn){
		$dbconn->query("delete from $tabname where $condition") or die($dbconn->errorInfo());
		return true;
	}


	/** 自定義SQL語句
	* @param String $sqlstr 	SQL語句
	* @param Array	$param	 	參數
	* @param obj	$dbconn		DB link
	*/
	public function handle($sqlstr,$param=array(),$dbconn){
		$type = explode(' ',$sqlstr);
		$type = strtolower($type[0]);
		switch($type){
			case 'select':
				$pagesize = isset($param['pagesize']) && !empty($param['pagesize'])? $param['pagesize'] : 0;
				$page = isset($param['page']) && !empty($param['page'])? $param['page'] : 1;
				$getrow = preg_match("/group by/i",$sqlstr)? 'rowCount' : 'fetch';
			
				if($pagesize>0){	//分頁
					$sqltotal = preg_replace('/^\s?select(.*)from/isU', 'SELECT count(*) from', $sqlstr, 1);
					$query = $dbconn->query($sqltotal)->$getrow();
					$total = is_array($query)? array_pop($query) : $query; //記錄總數
					$pagecount = ($total>0)? (int)(($total-1)/$pagesize)+1 : 0;
					if(!is_numeric($page) || $page<=0 || $page>$pagecount){
						$page = 1;
					}
					$pageinfo = array();
					$pageinfo['total'] = $total;
					$pageinfo['pagecount'] = $pagecount;
					$pageinfo['pagesize'] = $pagesize;
					$pageinfo['curpage'] = $page;
		
					$startpage = ($page-1)*$pagesize;
					$sqlstr = $sqlstr." limit $startpage,$pagesize";
				}
				$sth = $dbconn->prepare($sqlstr);
				$sth->execute();
				$result = $sth->fetchAll(PDO::FETCH_ASSOC);
				$data = array();
				$data['result'] = $result;
				$data['pageinfo'] = isset($pageinfo)? $pageinfo : array();
				return $data;
				break;

			case 'insert':
				$dbconn->query($sqlstr) or die($dbconn->errorInfo());
				return $dbconn->lastInsertId();
				break;

			case 'update':
				$sth = $dbconn->query($sqlstr) or die($dbconn->errorInfo());
				return $sth->rowCount();
				break;

			case 'delete':
				$dbconn->query($sqlstr) or die($dbconn->errorInfo());
				return true;
				break;

			case 'replace':
				$dbconn->query($sqlstr) or die($dbconn->errorInfo());
				return $dbconn->lastInsertId();
				break;
				
			default: 
				$dbconn->query($sqlstr) or die($dbconn->errorInfo());				
		}
	}


	/* 顯示sql語句
	* @param int 	$debug 0/1
	* @param String	$sql
	*/
	private function debug($debug,$sql){
		if($debug==1){
			echo $sql."<br>";
		}
	}

}
?>